如何跳出MySQL 的10個大坑?

MySQL

Posted by MySQL on 2019-10-11 18:00:00

MySQL · 性能優化· Group Commit優化

背景

關於Group Commit網上的資料其實已經足夠多了，我這裡只簡單的介紹一下。

眾所周知，在MySQL5.6之前的版本，由於引入了Binlog/InnoDB的XA，Binlog的寫入和InnoDB commit完全串行化執行，大概的執行序列如下：

 InnoDB prepare （持有prepare_commit_mutex）；
 write/sync Binlog；
 InnoDB commit (寫入COMMIT標記後釋放prepare_commit_mutex)。

當sync_binlog=1時，很明顯上述的第二步會成為瓶頸，而且還是持有全局大鎖，這也是為什麼性能會急劇下降。

 

很快Mariadb就提出了一個Binlog Group Commit方案，即在準備寫入Binlog時，維持一個隊列，最早進入隊列的是leader，後來的是follower，leader為蒐集到的隊列中的線程依次寫Binlog文件,並co​​mmit事務。Percona 的Group Commit實現也是Port自Mariadb。不過仍在使用Percona Server5.5的朋友需要注意，該Group Commit實現可能破壞掉Semisync的行為

Oracle MySQL 在5.6版本開始也支持Binlog Group Commit，使用了和Mariadb類似的思路，但將Group Commit的過程拆分成了三個階段：flush stage 將各個線程的binlog從cache寫到文件中; sync stage對binlog做fsync操作（如果需要的話）；commit stage 為各個線程做引擎層的事務commit。每個stage同時只有一個線程在操作。

Tips：當引入Group Commit後，sync_binlog的含義就變了，假定設為1000，表示的不是1000個事務後做一次fsync，而是1000個事務組。

Oracle MySQL的實現的優勢在於三個階段可以並發執行，從而提升效率。

XA Recover

在Binlog打開的情況下，MySQL默認使用MySQL_BIN_LOG來做XA協調者，大致流程為：

1.掃描最後一個Binlog文件，提取其中的xid；
2.InnoDB維持了狀態為Prepare的事務鍊錶，將這些事務的xid和Binlog中記錄的xid做比較，如果在Binlog中存在，則提交，否則回滾事務。

通過這種方式，可以讓InnoDB和Binlog中的事務狀態保持一致。顯然只要事務在InnoDB層完成了Prepare，並且寫入了Binlog，就可以從崩潰中恢復事務，這意味著我們無需在InnoDB commit時顯式的write/fsync redo log。

Tips：MySQL為何只需要掃描最後一個Binlog文件呢？原因是每次在rotate到新的Binlog文件時，總是保證沒有正在提交的事務，然後fsync一次InnoDB的redo log。這樣就可以保證老的Binlog文件中的事務在InnoDB總是提交的。

問題

其實問題很簡單：每個事務都要保證其Prepare的事務被write/fsync到redo log文件。儘管某個事務可能會幫助其他事務完成redo 寫入，但這種行為是隨機的，並且依然會產生明顯的log_sys->mutex開銷。

優化

從XA恢復的邏輯我們可以知道，只要保證InnoDB Prepare的redo日誌在寫Binlog前完成write/sync即可。因此我們對Group Commit的第一個stage的邏輯做了些許修改，大概描述如下：

Step1. InnoDB Prepare，記錄當前的LSN到thd中；
Step2.進入Group Commit的flush stage；Leader蒐集隊列，同時算出隊列中最大的LSN。
Step3.將InnoDB的redo log write/fsync到指定的LSN
Step4.寫Binlog並進行隨後的工作(sync Binlog, InnoDB commit , etc)

通過延遲寫redo log的方式，顯式的為redo log做了一次組寫入，並減少了log_sys->mutex的競爭。

目前官方MySQL已經根據我們report的bug#73202鎖提供的思路，對5.7.6的代碼進行了優化，對應的Release Note如下：

When using InnoDB with binary logging enabled, concurrent transactions written in the InnoDB redo log are now grouped together before synchronizing to disk when innodb_flush_log_at_trx_commit is set to 1, which reduces the amount of synchronization operations. This can lead to improved performance.

性能數據

簡單測試了下，使用sysbench, update_non_index.lua, 100張表，每張10w行記錄，innodb_flush_log_at_trx_commit=2, sync_binlog=1000，關閉Gtid

 並發線程原生修改後
 32 25600 27000
 64 30000 35000
 128 33000 39000
 256 29800 38000

MySQL · 新增特性· DDL fast fail

背景

項目的快速迭代開發和在線業務需要保持持續可用的要求，導致MySQL的ddl變成了DBA很頭疼的事情，而且經常導致故障發生。本篇介紹RDS分支上做的一個功能改進，DDL fast fail。主要解決：DDL操作因為無法獲取MDL排它鎖，進入等待隊列的時候，阻塞了應用所有的讀寫請求問題。

MDL鎖機制介紹

首先介紹一下MDL(METADATA LOCK)鎖機制，MySQL為了保證表結構的完整性和一致性，對錶的所有訪問都需要獲得相應級別的MDL鎖，比如以下場景：

session 1: start transaction; select * from test.t1;
session 2: alter table test.t1 add extra int;
session 3: select * from test.t1;

  • session 1對t1表做查詢，首先需要獲取t1表的MDL_SHARED_READ級別MDL鎖。鎖一直持續到commit結束，然後釋放。

  • session 2對t1表做DDL，需要獲取t1表的MDL_EXCLUSIVE級別MDL鎖，因為MDL_SHARED_READ與MDL_EXCLUSIVE不相容，所以session 2被session 1阻塞，然後進入等待隊列。

  • session 3對t1表做查詢，因為等待隊列中有MDL_EXCLUSIVE級別MDL鎖請求，所以session3也被阻塞，進入等待隊列。

這種場景就是目前因為MDL鎖導致的很經典的阻塞問題，如果session1長時間未提交，或者查詢持續過長時間，那麼後續對t1表的所有讀寫操作，都被阻塞。對於在線的業務來說，很容易導致業務中斷。

aliyun RDS分支改進

DDL fast fail並沒有解決真正DDL過程中的阻塞問題，但避免了因為DDL操作沒有獲取鎖，進而導致業務其他查詢/更新語句阻塞的問題。

其實現方式如下：

alter table test.t1 no_wait/wait 1 add extra int;
在ddl語句中，增加了no_wait/wait 1語法支持。

其處理邏輯如下：

首先嘗試獲取t1表的MDL_EXCLUSIVE級別的MDL鎖：

  • 當語句指定的是no_wait，如果獲取失敗，客戶端將得到報錯信息：ERROR : Lock wait timeout exceeded; try restarting transaction。

  • 當語句指定的是wait 1，如果獲取失敗，最多等待1s，然後得到報錯信息：ERROR : Lock wait timeout exceeded; try restarting transaction。

另外，除了alter語句以外，還支持rename，truncate，drop，optimize，create index等ddl操作。

與Oracle的比較

在Oracle 10g的時候，DDL操作經常會遇到這樣的錯誤信息：

ora-00054:resource busy and acquire with nowait specified 即DDL操作無法獲取表上面的排它鎖，而fast fail。

其實DDL獲取排他鎖的設計，需要考慮的就是兩個問題：

  1. 雪崩，如果你採用排隊阻塞的機制，那麼DDL如果長時間無法獲取鎖，就會導致應用的雪崩效應，對於高並發的業務，也是災難。

  2. 餓死，如果你採用強制式的機制，那麼要防止DDL一直無法獲取鎖的情況，在業務高峰期，可能DDL永遠無法成功。

在Oracle 11g的時候，引入了DDL_LOCK_TIMEOUT參數，如果你設置了這個參數，那麼DDL操作將使用排隊阻塞模式，可以在session和global級別設置， 給了用戶更多選擇。

MySQL · 性能優化· 啟用GTID場景的性能問題及優化

背景

MySQL從5.6版本開始支持GTID特性，也就是所謂全局事務ID，在整個複制拓撲結構內，每個事務擁有自己全局唯一標識。GTID包含兩個部分，一部分是實例的UUID，另一部分是實例內遞增的整數。

GTID的分配包含兩種方式，一種是自動分配，另外一種是顯式設置session.gtid_next，下面簡單介紹下這兩種方式：

自動分配

如果沒有設置session級別的變量gtid_next，所有事務都走自動分配邏輯。分配GTID發生在GROUP COMMIT的第一個階段，也就是flush stage，大概可以描述為：

  • Step 1：事務過程中，碰到第一條DML語句需要記錄Binlog時，分配一段Gtid事件的cache，但不分配實際的GTID

  • Step 2：事務完成後，進入commit階段，分配一個GTID並寫入Step1預留的Gtid事件中，該GTID必須保證不在gtid_owned集合和gtid_executed集合中。分配的GTID隨後被加入到gtid_owned集合中。

  • Step 3：將Binlog 從線程cache中刷到Binlog文件中。

  • Step 4：將GTID加入到gtid_executed集合中。

  • Step 5：在完成sync stage 和commit stage後，各個會話將其使用的GTID從gtid_owned中移除。

顯式設置

用戶通過設置session級別變量gtid_next可以顯式指定一個GTID，流程如下：

  • Step 1：設置變量gtid_next，指定的GTID被加入到gtid_owned集合中。

  • Step 2：執行任意事務SQL，在將binlog從線程cache刷到binlog文件後，將GTID加入到gtid_executed集合中。

  • Step 3：在完成事務COMMIT後，從gtid_owned中移除。

備庫SQL線程使用的就是第二種方式，因為備庫在apply主庫的日誌時，要保證GTID是一致的，SQL線程讀取到GTID事件後，就根據其中記錄的GTID來設置其gtid_next變量。

問題

由於在實例內，GTID需要保證唯一性，因此不管是操作gtid_executed集合和gtid_owned集合，還是分配GTID，都需要加上一個大鎖。我們的優化主要集中在第一種GTID分配方式。

對於GTID的分配，由於處於Group Commit的第一個階段，由該階段的leader線程為其follower線程分配GTID及刷Binlog，因此不會產生競爭。

而在Step 5，各個線程在完成事務提交後，各自去從gtid_owned集合中刪除其使用的gtid。這時候每個線程都需要獲取互斥鎖，很顯然，並發越高，這種競爭就越明顯，我們很容易從pt-pmp輸出中看到如下類似的trace：

 ha_commit_trans—>MySQL_BIN_LOG::commit—>MySQL_BIN_LOG::ordered_commit—>MySQL_BIN_LOG::finish_commit—>Gtid_state::update_owned_gtids_impl—>lock_sidno

這同時也會影響到GTID的分配階段，導致TPS在高並發場景下的急劇下降。

解決

實際上對於自動分配GTID的場景，並沒有必要維護gtid_owned集合。我們的修改也非常簡單，在自動分配一個GTID後，直接加入到gtid_executed集合中，避免維護gtid_owned，這樣事務提交時就無需去清理gtid_owned集合了，從而可以完全避免鎖競爭。

當然為了保證一致性，如果分配GTID後，寫入Binlog文件失敗，也需要從gtid_executed集合中刪除。不過這種場景非常罕見。

性能數據

使用sysbench，100張表，每張10w行記錄，update_non_index.lua，純內存操作，innodb_flush_log_at_trx_commit = 2，sync_binlog = 1000

 並發線程原生修改後
 32 24500 25000
 64 27900 29000
 128 30800 31500
 256 29700 32000
 512 29300 31700
 1024 27000 31000

從測試結果可以看到，優化前隨著並發上升，性能出現下降，而優化後則能保持TPS穩定。

MySQL · 捉蟲動態· InnoDB自增列重複值問題

問題重現

先從問題入手，重現下這個bug

use test;
drop table if exists t1;
create table t1(id int auto_increment, a int, primary key (id)) engine=innodb;
insert into t1 values (1,2);
insert into t1 values (null,2);
insert into t1 values (null,2);
select * from t1;
+----+------+
| id | a |
+----+------+
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
+----+------+
delete from t1 where id=2;
delete from t1 where id=3;
select * from t1;
+----+------+
| id | a |
+----+------+
| 1 | 2 |
+----+------+

這裡我們關閉MySQL，再啟動MySQL,然後再插入一條數據

insert into t1 values (null,2);
select * FROM T1;
+----+------+
| id | a |
+----+------+
| 1 | 2 |
+----+------+
| 2 | 2 |
+----+------+

我們看到插入了（2,2），而如果我沒有重啟，插入同樣數據我們得到的應該是（4,2)。上面的測試反映了MySQLd重啟後，InnoDB存儲引擎的表自增id可能出現重複利用的情況。

自增id重複利用在某些場景下會出現問題。依然用上面的例子，假設t1有個歷史表t1_history用來存t1表的歷史數據，那麼MySQLd重啟前,ti_history中可能已經有了（2,2）這條數據，而重啟後我們又插入了（ 2，2），當新插入的(2,2)遷移到歷史表時，會違反主鍵約束。

原因分析

InnoDB 自增列出現重複值的原因：

MySQL> show create table t1\G;
*************************** 1. row ******************** *******
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=innodb AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

建表時可以指定AUTO_INCREMENT值，不指定時默認為1，這個值表示當前自增列的起始值大小，如果新插入的數據沒有指定自增列的值，那麼自增列的值即為這個起始值。對於InnoDB表，這個值沒有持久到文件中。而是存在內存中（dict_table_struct.autoinc）。那麼又問，既然這個值沒有持久下來，為什麼我們每次插入新的值後， show create table t1看到AUTO_INCREMENT值是跟隨變化的。其實show create table t1是直接從dict_table_struct.autoinc取得的（ha_innobase::update_create_info）。

知道了AUTO_INCREMENT是實時存儲內存中的。那麼，MySQLd 重啟後，從哪裡得到AUTO_INCREMENT呢? 內存值肯定是丟失了。實際上MySQL採用執行類似select max(id)+1 from t1;方法來得到AUTO_INCREMENT。而這種方法就是造成自增id重複的原因。

MyISAM自增值

MyISAM也有這個問題嗎？MyISAM是沒有這個問題的。myisam會將這個值實時存儲在.MYI文件中（mi_state_info_write）。MySQLd重起後會從.MYI中讀取AUTO_INCREMENT值（mi_state_info_read)。因此，MyISAM表重啟是不會出現自增id重複的問題。

問題修復

MyISAM選擇將AUTO_INCREMENT實時存儲在.MYI文件頭部中。實際上.MYI頭部還會實時存其他信息，也就是說寫AUTO_INCREMENT只是個順帶的操作，其性能損耗可以忽略。InnoDB 表如果要解決這個問題，有兩種方法。

1）將AUTO_INCREMENT最大值持久到frm文件中。
2）將AUTO_INCREMENT最大值持久到聚集索引根頁trx_id所在的位置。

第一種方法直接寫文件性能消耗較大，這是一額外的操作，而不是一個順帶的操作。我們採用第二種方案。為什麼選擇存儲在聚集索引根頁頁頭trx_id，頁頭中存儲trx_id,只對二級索引頁和insert buf 頁頭有效（MVCC)。而聚集索引根頁頁頭trx_id這個值是沒有使用的，始終保持初始值0。正好這個位置8個字節可存放自增值的值。我們每次更新AUTO_INCREMENT值時，同時將這個值修改到聚集索引根頁頁頭trx_id的位置。這個寫操作跟真正的數據寫操作一樣，遵守write-ahead log原則，只不過這裡只需要redo log ,而不需要undo log。因為我們不需要回滾AUTO_INCREMENT的變化（即回滾後自增列值會保留，即使insert 回滾了，AUTO_INCREMENT值不會回滾）。

因此，AUTO_INCREMENT值存儲在聚集索引根頁trx_id所在的位置，實際上是對內存根頁的修改和多了一條redo log（量很小）,而這個redo log 的寫入也是異步的，可以說是原有事務log的一個順帶操作。因此AUTO_INCREMENT值存儲在聚集索引根頁這個性能損耗是極小的。

修復後的性能對比，我們新增了全局參數innodb_autoinc_persistent 取值on/off； on 表示將AUTO_INCREMENT值實時存儲在聚集索引根頁。off則採用原有方式只存儲在內存。

./bin/sysbench --test=sysbench/tests/db/insert.lua --MySQL-port=4001 --MySQL-user=root \--MySQL-table-engine=innodb --MySQL-db=sbtest - -oltp-table-size=0 --oltp-tables-count=1 \--num-threads=100 --MySQL-socket=/u01/zy/sysbench/build5/run/MySQL.sock --max-time =7200 --max-requests run
set global innodb_autoinc_persistent=off;
tps: 22199 rt:2.25ms
set global innodb_autoinc_persistent=on;
tps: 22003 rt:2.27ms

可以看出性能損耗在%1以下。

改進

新增參數innodb_autoinc_persistent_interval 用於控制持久化AUTO_INCREMENT值的頻率。例如：innodb_autoinc_persistent_interval=100，auto_incrememt_increment=1時，即每100次insert會控制持久化一次AUTO_INCREMENT值。每次持久的值為：當前值+innodb_autoinc_persistent_interval。

測試結論

innodb_autoinc_persistent=ON, innodb_autoinc_persistent_interval=1時性能損耗在%1以下。
innodb_autoinc_persistent=ON, innodb_autoinc_persistent_interval=100時性能損耗可以忽略。

限制

  1. innodb_autoinc_persistent=on， innodb_autoinc_persistent_interval=N>1時，自增N次後持久化到聚集索引根頁,每次持久的值為當前AUTO_INCREMENT+(N-1)*innodb_autoextend_increment。重啟後讀取持久化的AUTO_INCREMENT值會偏大，造成一些浪費但不會重複。innodb_autoinc_persistent_interval=1 每次都持久化沒有這個問題。

  2. 如果innodb_autoinc_persistent=on，頻繁設置auto_increment_increment的可能會導致持久化到聚集索引根頁的值不准確。因為innodb_autoinc_persistent_interval計算沒有考慮auto_increment_increment變化的情況，參看dict_table_autoinc_update_if_greater。而設置auto_increment_increment的情況極少，可以忽略。

注意：如果我們使用需要開啟innodb_autoinc_persistent，應該在參數文件中指定

innodb_autoinc_persistent= on

如果這樣指定set global innodb_autoinc_persistent=on;重啟後將不會從聚集索引根頁讀取AUTO_INCREMENT最大值。

疑問：對於InnoDB表，重啟通過select max(id)+1 from t1得到AUTO_INCREMENT值，如果id上有索引那麼這個語句使用索引查找就很快。那麼，這個可以解釋MySQL 為什麼要求自增列必須包含在索引中的原因。如果沒有指定索引，則報如下錯誤，

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key 而myisam表竟然也有這個要求，感覺是多餘的。

MySQL · 優化改進· 複製性能改進過程

前言

與oracle 不同，MySQL 的主庫與備庫的同步是通過binlog 實現的，而redo日誌只做為MySQL 實例的crash recovery使用。MySQL在4.x 的時候放棄redo 的同步策略而引​​入binlog的同步，一個重要原因是為了兼容其它非事務存儲引擎，否則主備同步是沒有辦法進行的。

redo 日誌同步屬於物理同步方法，簡單直接，將修改的物理部分傳送到備庫執行，主備共用一致的LSN，只要保證LSN 相同即可，同一時刻，只能主庫或備庫一方接受寫請求； binlog的同步方法屬於邏輯複製，分為statement 或row 模式，其中statement記錄的是SQL語句，Row 模式記錄的是修改之前的記錄與修改之後的記錄，即前鏡像與後鏡像；備庫通過binlog dump 協議拉取binlog,然後在備庫執行。如果拉取的binlog是SQL語句，備庫會走和主庫相同的邏輯，如果是row 格式，則會調用存儲引擎來執行相應的修改。

本文簡單說明5.5到5.7的主備複製性能改進過程。

replication improvement (from 5.5 to 5.7)

(1) 5.5 中，binlog的同步是由兩個線程執行的

io_thread: 根據binlog dump協議從主庫拉取binlog, 並將binlog轉存到本地的relaylog；

sql_thread: 讀取relaylog，根據位點的先後順序執行binlog event，進而將主庫的修改同步到備庫，達到主備一致的效果； 由於在主庫的更新是由多個客戶端執行的，所以當壓力達到一定的程度時，備庫單線程執行主庫的binlog跟不上主庫執行的速度，進而會產生延遲造成備庫不可用，這也是分庫的原因之一，其SQL線程的執行堆棧如下：

sql_thread:
exec_relay_log_event
    apply_event_and_update_pos
         apply_event
             rows_log_event::apply_event
                 storage_engine operation
         update_pos

(2) 5.6中，引入了多線程模式，在多線程模式下，其線程結構如下

io_thread: 同5.5

Coordinator_thread: 負責讀取relay log，將讀取的binlog event以事務為單位分發到各個worker thread 進行執行，並在必要時執行binlog event（Description_format_log_event， Rotate_log_event 等）。

worker_thread: 執行分配到的binlog event，各個線程之間互不影響；

多線程原理

sql_thread 的分發原理是依據當前事務所操作的數據庫名稱來進行分發，如果事務是跨數據庫行為的，則需要等待已分配的該數據庫的事務全部執行完畢，才會繼續分發，其分配行為的偽碼可以簡單的描述如下：

get_slave_worker
  if (contains_partition_info(log_event))
     db_name= get_db_name(log_event);
     entry {db_name, worker_thread, usage} = map_db_to_worker(db_name);
     while (entry->usage > 0)
        wait();
    return worker;
  else if (last_assigned_worker)
    return last_assigned_worker;
  else
    push into buffer_array and deliver them until come across a event that have partition info

需要注意的細節

  • 內存的分配與釋放。relay thread 每讀取一個log_event, 則需要malloc 一定的內存，在work線程執行完後，則需要free掉；

  • 數據庫名與worker 線程的綁定信息在一個hash表中進行維護，hash表以entry為單位，entry中記錄當前entry所代表的數據庫名，有多少個事務相關的已被分發，執行這些事務的worker thread等信息；

  • 維護一個綁定信息的array , 在分發事務的時候，更新綁定信息，增加相應entry->usage, 在執行完一個事務的時候，則需要減少相應的entry->usage；

  • slave worker 信息的維護，即每個worker thread執行了哪些事務，執行到的位點是在哪，延遲是如何計算的，如果執行出錯，mts_recovery_group 又是如何恢復的；

  • 分配線程是以數據庫名進行分發的，當一個實例中只有一個數據庫的時候，不會對性能有提高，相反，由於增加額外的操作，性能還會有一點回退；

  • 臨時表的處理，臨時表是和entry綁定在一起的，在執行的時候將entry的臨時表掛在執行線程thd下面，但沒有固化，如果在臨時表操作期間，備庫crash，則重啟後備庫會有錯誤；

總體上說，5.6 的並行複制打破了5.5 單線程的複制的行為，只是在單庫下用處不大，並且5.6的並行複制的改動引入了一些重量級的bug

  • MySQL slave sql thread memory leak (http://bugs.MySQL.com/bug.php?id=71197)

  • Relay log without xid_log_event may case parallel replication hang (http://bugs.MySQL.com/bug.php?id=72794)

  • Transaction lost when relay_log_info_repository=FILE and crashed (http://bugs.MySQL.com/bug.php?id=73482)

(3) 5.7中，並行複制的實現添加了另外一種並行的方式，即主庫在ordered_commit中的第二階段的時候，將同一批commit的binlog 打上一個相同的seqno標籤，同一時間戳的事務在備庫是可以同時執行的，因此大大簡化了並行複制的邏輯，並打破了相同DB 不能並行執行的限制。備庫在執行時，具有同一seqno的事務在備庫可以並行的執行，互不干擾，也不需要綁定信息，後一批seqno的事務需要等待前一批相同seqno的事務執行完後才可以執行。